using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.ComponentModel;
using System.Data;
using System.Reflection;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Windows.Forms;

namespace THSLib
{
    public class DALBaseProvider
    {
        protected string _tableName;
        protected Type _objectType;

        #region Public Properties
        public string TableName
        {
            get
            {
                return _tableName;
            }
            set
            {
                _tableName = value;
            }
        }

        public Type ObjectType
        {
            get
            {
                return _objectType;
            }
            set
            {
                _objectType = value;
            }
        }
        #endregion

        #region -- Constractor--
        public DALBaseProvider()
        {

        }
        public DALBaseProvider(string strTableName, Type objType)
        {
            this._tableName = strTableName;
            this._objectType = objType;
        }
        #endregion

        #region Function Generate: Insert,Update,Select Query for Table
        private string GenerateInsertQuery()
        {
            string strInsertQuery = string.Format("INSERT INTO [dbo].[{0}] ", TableName);
            DataSet dsTableColumns = SqlDbHelper.GetAllTableColumns(TableName);
            if (dsTableColumns.Tables.Count > 0)
            {
                strInsertQuery = strInsertQuery + GenerateInsertColumns(dsTableColumns) + " VALUES " + GenerateInsertColumnParameters(dsTableColumns);
            }
            return strInsertQuery;
        }

        private string GenerateInsertColumns(DataSet dsTableColumns)
        {
            string strInsertColumns = "(";
            for (int i = 0; i < dsTableColumns.Tables[0].Rows.Count; i++)
            {
                if (i == dsTableColumns.Tables[0].Rows.Count - 1)
                    strInsertColumns = strInsertColumns + "[" + dsTableColumns.Tables[0].Rows[i]["COLUMN_NAME"].ToString() + "])";
                else
                    strInsertColumns = strInsertColumns + "[" + dsTableColumns.Tables[0].Rows[i]["COLUMN_NAME"].ToString() + "],";
            }
            return strInsertColumns;

        }

        private string GenerateInsertColumnParameters(DataSet dsTableColumns)
        {
            string strInsertColumnParameters = "(";
            for (int i = 0; i < dsTableColumns.Tables[0].Rows.Count; i++)
            {
                if (i == dsTableColumns.Tables[0].Rows.Count - 1)
                    strInsertColumnParameters = strInsertColumnParameters + "@" + dsTableColumns.Tables[0].Rows[i]["COLUMN_NAME"].ToString() + ")";
                else
                    strInsertColumnParameters = strInsertColumnParameters + "@" + dsTableColumns.Tables[0].Rows[i]["COLUMN_NAME"].ToString() + ",";
            }
            return strInsertColumnParameters;
        }

        private string GenerateUpdateQuery()
        {
            string strUpdateQuery = string.Format("UPDATE [dbo].[{0}] SET ", TableName);

            DataSet dsTableColumns = SqlDbHelper.GetAllTableColumns(TableName);
            if (dsTableColumns.Tables.Count > 0)
            {
                strUpdateQuery = strUpdateQuery + GenerateUpdateSetStatement(dsTableColumns) + " WHERE " + GenerateUpdateQueryWhereClause(dsTableColumns);
            }
            return strUpdateQuery;
        }

        private string GenerateUpdateSetStatement(DataSet dsTableColumns)
        {
            string strUpdateSetStatement = string.Empty;
            for (int i = 0; i < dsTableColumns.Tables[0].Rows.Count; i++)
            {
                string strColumnName = dsTableColumns.Tables[0].Rows[i]["COLUMN_NAME"].ToString();
                if (!SqlDbHelper.ColumnIsPrimaryKey(TableName, strColumnName))
                {
                    if (i == dsTableColumns.Tables[0].Rows.Count - 1)
                        strUpdateSetStatement = strUpdateSetStatement + "[" + strColumnName + "] = @" + strColumnName;
                    else
                        strUpdateSetStatement = strUpdateSetStatement + "[" + strColumnName + "] = @" + strColumnName + ",";
                }
            }
            return strUpdateSetStatement;
        }

        private string GenerateUpdateQueryWhereClause(DataSet dsTableColumns)
        {
            string strWhereClause = string.Empty;
            string strPrimaryKeyColumn = SqlDbHelper.GetPrimaryKeyColumn(TableName);
            strWhereClause = strWhereClause + "[" + strPrimaryKeyColumn + "]" + " = @" + strPrimaryKeyColumn;
            return strWhereClause;
        }

        private string GenerateSelectAllQuery()
        {
            return string.Format("SELECT * FROM [dbo].[{0}]", TableName);
        }

        private string GenerateSelectByPrimaryKeyQuery()
        {
            return string.Format("SELECT * FROM [dbo].[{0}] WHERE [{1}]=@{2}", TableName, SqlDbHelper.GetPrimaryKeyColumn(TableName), SqlDbHelper.GetPrimaryKeyColumn(TableName));
        }

        private string GenerateDeleteQuery()
        {
            return string.Format("DELETE FROM [dbo].[{0}] WHERE [{1}]=@{2}", TableName, SqlDbHelper.GetPrimaryKeyColumn(TableName), SqlDbHelper.GetPrimaryKeyColumn(TableName));
        }

        private string GenerateDeleteAllObjectsQuery()
        {
            return string.Format("DELETE FROM [dbo].[{0}]", TableName);
        }

        #endregion

        #region Function for Generate Insert,Update,Select Stored Procedure Name
        private string GenerateInsertStoredProcedureName()
        {
            return string.Format("{0}_Insert", TableName);
        }

        private string GenerateUpdateStoredProcedureName()
        {
            return string.Format("{0}_Update", TableName);
        }

        private string GenerateDeleteStoredProcedureName()
        {
            return string.Format("{0}_Delete", TableName);
        }

        private string GenerateDeleteByForeignColumnStoredProcedureName(String strForeignColumn)
        {
            return string.Format("{0}_DeleteBy{1}", TableName, strForeignColumn);
        }

        private string GenerateDeleteAllStoredProcedureName()
        {
            return string.Format("{0}_DeleteAll", TableName);
        }

        private string GenerateSelectAllStoredProcedureName()
        {
            return string.Format("{0}_SelectAll", TableName);
        }

        private string GenerateSelectAllProfilesStoredProcedureName()
        {
            return string.Format("{0}_SelectAllProfiles", TableName);
        }

        private string GenerateSelectAllAlivesStoredProcedureName()
        {
            return string.Format("{0}_SelectAlive{0}", TableName);
        }

        private string GenerateSelectByPrimaryKeyStoredProcedureName()
        {
            return string.Format("{0}_Select", TableName);
        }

        private string GenerateSelectDeletedByPrimayKeyStoredProcedureName()
        {
            return string.Format("{0}_SelectDeletedByID", TableName);
        }

        private string GenerateSelectByNameStoredProcedureName()
        {
            return string.Format("{0}_SelectByName", TableName);
        }

        private string GenerateSelectByNoStoredProcedureName()
        {
            return string.Format("{0}_SelectByNo", TableName);
        }

        private string GenerateSelectByAANumberIntStoredProcedureName()
        {
            return string.Format("{0}_SelectByAANumberInt", TableName);
        }

        private string GenerateSelectNewObjectInSessionStoredProcedureName()
        {
            return string.Format("{0}_SelectNewInSession", TableName);
        }

        private string GenerateUpdateObjectSaveStatusInSessionStoredProcedureName()
        {
            return string.Format("{0}_UpdateSaveStatusInSession", TableName);
        }

        private string GenerateSearchObjectStoredProcedureName()
        {
            return string.Format("{0}_Search", TableName);
        }

        private string GenerateSelectByForeignColumnStoredProcedureName(String strForeignColumnName)
        {
            return string.Format("{0}_SelectBy{1}", TableName, strForeignColumnName);
        }

        #endregion

        #region Utility Functions
        public void SetValueToPrimaryColumn(object obj, int iObjectID)
        {
            SqlDbHelper.SetValueToPrimaryColumn(obj, this, iObjectID);
        }

        public void SetValueToIDStringColumn(object obj, int iObjectID)
        {
            SqlDbHelper.SetValueToIDStringColumn(obj, this, iObjectID);
        }
        

        public object GetPrimaryColumnValue(object obj)
        {
            return SqlDbHelper.GetPrimaryColumnValue(obj, this);
        }

        public virtual int GetMaxID()
        {
            return SqlDbHelper.GetMaxID(TableName);
        }
        
        #endregion

        #region Transaction Functions
        public DbTransaction BeginTransaction()
        {
            return SqlDbHelper.BeginTransaction();
        }

        public void CommitTransaction(DbTransaction transaction)
        {
            SqlDbHelper.CommitTransaction(transaction);
        }

        public void RollbackTransaction(DbTransaction transaction)
        {
            SqlDbHelper.RollbackTransaction(transaction);
        }
        #endregion

        #region Create,Update,Delelte Functions
        public virtual int CreateObject(object obj)
        {
            DateTime now = DateTime.Now;
            THSDbUtil.SetPropertyValue(obj, "AACreatedDate", now);
            THSDbUtil.SetPropertyValue(obj, "AAUpdatedDate", now);
            return SqlDbHelper.InsertObject(obj, this, GenerateInsertStoredProcedureName());
        }

        public virtual int CreateObject(object obj, DbTransaction transaction)
        {
            DateTime now = DateTime.Now;
            THSDbUtil.SetPropertyValue(obj, "AACreatedDate", now);
            THSDbUtil.SetPropertyValue(obj, "AAUpdatedDate", now);
            return SqlDbHelper.InsertObject(obj, this, GenerateInsertStoredProcedureName(), transaction);
        }

        public virtual int UpdateObject(object obj)
        {
            DateTime now = DateTime.Now;
            THSDbUtil.SetPropertyValue(obj, "AAUpdatedDate", now);
            return SqlDbHelper.InsertObject(obj, this, GenerateUpdateStoredProcedureName());
        }

        public virtual int UpdateObject(object obj, DbTransaction transaction)
        {
            DateTime now = DateTime.Now;
            THSDbUtil.SetPropertyValue(obj, "AAUpdatedDate", now);
            return SqlDbHelper.InsertObject(obj, this, GenerateUpdateStoredProcedureName(), transaction);
        }

        public virtual void DeleteObject(int iObjectID)
        {
            SqlDbHelper.TableName = this.TableName;
            DbCommand cmd = SqlDbHelper.GetStoredProcedure(GenerateDeleteStoredProcedureName());
            SqlDbHelper.AddInParameter(cmd, SqlDbHelper.GetPrimaryKeyColumn(TableName), SqlDbType.Int, iObjectID);
            SqlDbHelper.RunStoredProcedure(cmd);
        }

        public virtual void DeleteObject(int iObjectID, DbTransaction transaction)
        {
            try
            {
                SqlDbHelper.TableName = this.TableName;
                DbCommand cmd = SqlDbHelper.GetStoredProcedure(GenerateDeleteStoredProcedureName());
                SqlDbHelper.AddInParameter(cmd, SqlDbHelper.GetPrimaryKeyColumn(TableName), SqlDbType.Int, iObjectID);
                cmd.Transaction = transaction;
                SqlDbHelper.RunStoredProcedure(cmd);
                SqlDbHelper.CommitTransaction(transaction);
            }
            catch (Exception)
            {
                SqlDbHelper.RollbackTransaction(transaction);
            }
        }

        public virtual void DeleteAllObjects()
        {
            SqlDbHelper.TableName = this.TableName;
            SqlDbHelper.RunStoredProcedure(GenerateDeleteAllStoredProcedureName());
        }

        public virtual void DeleteByForeignColumn(String strForeignColumn, object objValue)
        {
            SqlDbHelper.TableName = this.TableName;
            SqlDbHelper.RunStoredProcedure(GenerateDeleteByForeignColumnStoredProcedureName(strForeignColumn), objValue);
        }
        public void DeleteAllObjectsByObjectParentID(int iObjectParentID)
        {
            SqlDbHelper.TableName = this.TableName;
            string primaryKeyColumn = SqlDbHelper.GetPrimaryKeyColumn(this.TableName);
            string strColumnName = "FK_" + primaryKeyColumn.Substring(0, primaryKeyColumn.Length - 2) + "ParentID";
            if (SqlDbHelper.ColumnIsExistInTable(this.TableName, strColumnName))
            {
                SqlDbHelper.RunQuery(SqlDbHelper.GetQuery(string.Format("Delete From [{0}] Where ([{1}]={2}) And ({2}>0)", this.TableName, strColumnName, iObjectParentID)));
            }
        }
        #endregion

        #region Get Functions
        public virtual object GetObjectById(int iObjectID)
        {
            SqlDbHelper.TableName = this.TableName;
            DbCommand cmd = SqlDbHelper.GetStoredProcedure(GenerateSelectByPrimaryKeyStoredProcedureName());
            SqlDbHelper.AddInParameter(cmd, SqlDbHelper.GetPrimaryKeyColumn(TableName), SqlDbType.Int, iObjectID);
            DataSet ds = SqlDbHelper.RunStoredProcedure(cmd);
            if (ds.Tables.Count <= 0)
                return null;
            return SqlDbHelper.GetSingleObject(ds.Tables[0], ObjectType);
        }

        public virtual DataSet GetDataSetByID(int iObjectID)
        {

            SqlDbHelper.TableName = this.TableName;
            DbCommand cmd = SqlDbHelper.GetStoredProcedure(GenerateSelectByPrimaryKeyStoredProcedureName());
            SqlDbHelper.AddInParameter(cmd, SqlDbHelper.GetPrimaryKeyColumn(TableName), SqlDbType.Int, iObjectID);
            DataSet ds = SqlDbHelper.RunStoredProcedure(cmd);
            return ds;
        }

        public virtual String GetObjectNameByID(int iObjectID)
        {
            SqlDbHelper.TableName = this.TableName;
            object obj = GetObjectById(iObjectID);
            if (obj != null)
            {
                String strObjectNameColumn = SqlDbHelper.GetPrimaryKeyColumn(TableName).Substring(0, SqlDbHelper.GetPrimaryKeyColumn(TableName).Length - 2) + "Name";
                PropertyInfo property = ObjectType.GetProperty(strObjectNameColumn);
                if (property != null)
                {
                    return property.GetValue(obj, null).ToString();
                }
            }
            return String.Empty;
        }

        public virtual String GetObjectNoByID(int iObjectID)
        {
            SqlDbHelper.TableName = this.TableName;
            object obj = GetObjectById(iObjectID);
            if (obj != null)
            {
                String strObjectNoColumn = SqlDbHelper.GetPrimaryKeyColumn(TableName).Substring(0, SqlDbHelper.GetPrimaryKeyColumn(TableName).Length - 2) + "No";
                PropertyInfo property = ObjectType.GetProperty(strObjectNoColumn);
                if (property != null)
                {
                    return property.GetValue(obj, null).ToString();
                }
            }
            return String.Empty;
        }        

        public virtual object GetObjectByName(String strObjectName)
        {
            try
            {
                SqlDbHelper.TableName = this.TableName;
                DataSet ds = SqlDbHelper.RunStoredProcedure(GenerateSelectByNameStoredProcedureName(), strObjectName);
                if (ds.Tables.Count > 0)
                {
                    return SqlDbHelper.GetSingleObject(ds.Tables[0], ObjectType);
                }
            }
            catch (Exception)
            {                
                return null;
            }
            return null;
        }

        public virtual object GetObjectByNo(String strObjectNo)
        {
            try
            {
                SqlDbHelper.TableName = this.TableName;
                DataSet ds = SqlDbHelper.RunStoredProcedure(GenerateSelectByNoStoredProcedureName(), strObjectNo);
                if (ds.Tables.Count > 0)
                {
                    return SqlDbHelper.GetSingleObject(ds.Tables[0], ObjectType);
                }
            }
            catch (Exception)
            {
                return null;
            }
            return null;
        }

        public virtual object GetObjectByAANumberInt(int iAANumberInt)
        {
            try
            {
                SqlDbHelper.TableName = this.TableName;
                DataSet ds = SqlDbHelper.RunStoredProcedure(GenerateSelectByAANumberIntStoredProcedureName(), iAANumberInt);
                if (ds.Tables.Count > 0)
                {
                    return SqlDbHelper.GetSingleObject(ds.Tables[0], ObjectType);
                }
            }
            catch (Exception)
            {
                return null;
            }
            return null;
        }

        public virtual int GetObjectIDByName(String strObjectName)
        {
            int iObjectID = 0;
            SqlDbHelper.TableName = this.TableName;
            object obj = GetObjectByName(strObjectName);
            if (obj != null)
            {
                iObjectID = Convert.ToInt32(SqlDbHelper.GetPrimaryColumnValue(obj, this));
            }
            return iObjectID;
        }

        public virtual int GetObjectIDByNo(String strObjectNo)
        {
            int iObjectID = 0;
            SqlDbHelper.TableName = this.TableName;
            object obj = GetObjectByNo(strObjectNo);
            if (obj != null)
            {
                iObjectID = Convert.ToInt32(SqlDbHelper.GetPrimaryColumnValue(obj, this));
            }
            return iObjectID;
        }

        public virtual object GetDeletedObjectByID(int iObjectID)
        {
            SqlDbHelper.TableName = this.TableName;
            DbCommand cmd = SqlDbHelper.GetStoredProcedure(GenerateSelectDeletedByPrimayKeyStoredProcedureName());
            SqlDbHelper.AddInParameter(cmd, SqlDbHelper.GetPrimaryKeyColumn(TableName), SqlDbType.Int, iObjectID);
            DataSet ds = SqlDbHelper.RunStoredProcedure(cmd);
            if (ds.Tables.Count <= 0)
                return null;
            return SqlDbHelper.GetSingleObject(ds.Tables[0], ObjectType);
        }

        public DataSet GetDataSet(string spName, params object[] paramValues)
        {
            SqlDbHelper.TableName = this.TableName;
            return (DataSet)SqlDbHelper.RunStoredProcedure(spName, paramValues);

        }

        public DataSet GetDataSet(String strQuery)
        {
            SqlDbHelper.TableName = this.TableName;
            DbCommand cmd = SqlDbHelper.GetQuery(strQuery);
            return (DataSet)SqlDbHelper.RunQuery(cmd);
        }

        public virtual object GetTemplateObject()
        {
            SqlDbHelper.TableName = this.TableName;
            String strSQL = String.Format("Select * From [dbo].[{0}] Where [{1}]='Template'", TableName,SqlDbHelper.AAStatusColumn);
            DbCommand cmd = SqlDbHelper.GetQuery(strSQL);
            DataSet ds = SqlDbHelper.RunQuery(cmd);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return SqlDbHelper.GetObjectFromDataRow(ds.Tables[0].Rows[0], ObjectType);
                }
            }
            return null;
        }

        public object GetDataObject(string spName, params object[] paramValues)
        {
            SqlDbHelper.TableName = this.TableName;
            DataSet ds = SqlDbHelper.RunStoredProcedure(spName, paramValues);
            return GetSingleObject(ds.Tables[0]);
        }

        public object GetSingleObject(DataTable dt)
        {
            SqlDbHelper.TableName = this.TableName;
            return SqlDbHelper.GetSingleObject(dt, ObjectType);
        }

        public object GetFirstObject()
        {
            SqlDbHelper.TableName = this.TableName;
            DataSet ds = GetAllObject();
            return GetSingleObject(ds.Tables[0]);
        }

        public object GetObjectFromDataRow(DataRow row)
        {
            SqlDbHelper.TableName = this.TableName;
            return SqlDbHelper.GetObjectFromDataRow(row, ObjectType);
        }

        public DataSet GetNewObjectInSession(String strUserName)
        {
            SqlDbHelper.TableName = this.TableName;
            return (DataSet)SqlDbHelper.RunStoredProcedure(GenerateSelectNewObjectInSessionStoredProcedureName(), strUserName);
        }

        public void UpdateObjectSaveStatusInSession(String strUserName)
        {
            SqlDbHelper.TableName = this.TableName;
            SqlDbHelper.RunStoredProcedure(GenerateUpdateObjectSaveStatusInSessionStoredProcedureName(), strUserName);
        }

        public DataSet SearchObject(object objSearch)
        {
            SqlDbHelper.TableName = this.TableName;
            DbCommand cmd = SqlDbHelper.GetStoredProcedure(GenerateSearchObjectStoredProcedureName());
            SqlDbHelper.AddParameterForSearchProperties(objSearch, cmd);
            return (DataSet)SqlDbHelper.RunStoredProcedure(cmd);
        }
        public DataSet GetObjectsByUpdatedDate(String strTableName, DateTime dateValue)
        {
            String strAACreatedDate = "AACreatedDate";
            String strAAUpdateDate = "AAUpdatedDate";
            String strFormatDate = dateValue.ToString("yyyy-MM-dd hh:mm:ss");
            String strSqlQuery = String.Format("SELECT * FROM [dbo].[{0}] WHERE CONVERT(VARCHAR(20), [{1}], 120) > CONVERT(VARCHAR(20), '{2}', 120) AND  CONVERT(VARCHAR(20), {1}, 120) <  CONVERT(VARCHAR(20), '9999-12-31 00:00:00', 120)  ", strTableName, strAAUpdateDate, strFormatDate);
            DbCommand cmd = SqlDbHelper.GetQuery(strSqlQuery);
            return (DataSet)SqlDbHelper.RunQuery(cmd);
        }
        //hieu nguyen created Jan,19,2009
        //get new object by created date
        public DataSet GetNewObjectsByAACreatdDate(String strTableName, DateTime dateValue)
        {
            String strAACreatedDate = "AACreatedDate";
            string formatDate = dateValue.ToString("yyyy-MM-dd hh:mm:ss");
            String strQuery = String.Format("SELECT * FROM [dbo].{0} WHERE CONVERT(VARCHAR(20), {1}, 120) > CONVERT(VARCHAR(20),'{2}', 120)", strTableName, strAACreatedDate, formatDate);
            DbCommand cmd = SqlDbHelper.GetQuery(strQuery);
            return (DataSet)SqlDbHelper.RunQuery(cmd);
        }
        public DataSet GetAllDataByForeignColumn(String strForeignColumnName, object objValue)
        {
            SqlDbHelper.TableName = this.TableName;
            return (DataSet)SqlDbHelper.RunStoredProcedure(GenerateSelectByForeignColumnStoredProcedureName(strForeignColumnName), objValue);

        }

        public virtual DataSet GetAllProfileObjects()
        {
            SqlDbHelper.TableName = this.TableName;
            return SqlDbHelper.RunStoredProcedure(GenerateSelectAllProfilesStoredProcedureName());
        }

        public virtual DataSet GetAllObject()
        {
            SqlDbHelper.TableName = this.TableName;
            return SqlDbHelper.RunStoredProcedure(GenerateSelectAllStoredProcedureName());
        }

        public virtual DataSet GetAllAliveObjects()
        {
            SqlDbHelper.TableName = this.TableName;
            return SqlDbHelper.RunStoredProcedure(GenerateSelectAllAlivesStoredProcedureName());
        }



        #endregion

        #region Function for get,delete all record From parent
        public DataSet GetAllObjectsByObjectParentID(int iObjectParentID)
        {
            SqlDbHelper.TableName = this.TableName;
            String strPrimaryKey = SqlDbHelper.GetPrimaryKeyColumn(TableName);
            String strParentObjectIDForeignKey = strPrimaryKey.Substring(0, strPrimaryKey.Length - 2) + "ParentID";
            String strQuery = String.Format("Select * From [{0}] Where AAStatus = 'Alive' And ([{1}]={2}) And ({2}>0)", TableName, strParentObjectIDForeignKey, iObjectParentID);
            DbCommand cmd = SqlDbHelper.GetQuery(strQuery);
            return (DataSet)SqlDbHelper.RunQuery(cmd);
        }

       
        #endregion

        #region Functions For Get,Delete Members From Owner and Switcher table
        public DataSet GetMembersFromOwner(String strOwnerTable, int iOwnerID, String strMemberTable, String strSwitcherTable)
        {
            SqlDbHelper.TableName = this.TableName;
            String strOwnerPrimaryColumn=SqlDbHelper.GetPrimaryKeyColumn(strOwnerTable);
            String strSwitcherPrimaryColumn=SqlDbHelper.GetPrimaryKeyColumn(strSwitcherTable);
            String strMemberPrimaryColumn=SqlDbHelper.GetPrimaryKeyColumn(strMemberTable);

            String strSwitcherQuery = String.Format(
                                    "Select [{0}] From [{1}] Where [{1}].[{2}] IN (Select [{2}] From [{3}] Where [{3}].[{2}]={4})",
                                    strMemberPrimaryColumn, strSwitcherTable,
                                    strOwnerPrimaryColumn, strOwnerTable, iOwnerID);
            String strMemberQuery = String.Format("Select * From [{0}] Where [{1}] IN ({2})",
                                                strMemberTable, strMemberPrimaryColumn, strSwitcherQuery);
            DbCommand cmd = SqlDbHelper.GetQuery(strMemberQuery);
            return (DataSet)SqlDbHelper.RunQuery(cmd);            
        }

        public DataSet DeleteMembersFromOwner(String strOwnerTable, int iOwnerID, String strMemberTable, String strSwitcherTable)
        {
            SqlDbHelper.TableName = this.TableName;
            String strOwnerPrimaryColumn = SqlDbHelper.GetPrimaryKeyColumn(strOwnerTable);
            String strSwitcherPrimaryColumn = SqlDbHelper.GetPrimaryKeyColumn(strSwitcherTable);
            String strMemberPrimaryColumn = SqlDbHelper.GetPrimaryKeyColumn(strMemberTable);

            String strSwitcherQuery = String.Format(
                                    "Select [{0}] From [{1}] Where [{1}].[{2}] IN (Select [{2}] From [{3}] Where [{3}].[{2}]={4}))",
                                    strMemberPrimaryColumn, strSwitcherTable,
                                    strOwnerPrimaryColumn, strOwnerTable, iOwnerID);
            String strMemberQuery = String.Format(" Detete From [{0}] Where [{1}] IN ({2})",
                                                strMemberTable, strMemberPrimaryColumn, strSwitcherQuery);
            DbCommand cmd = SqlDbHelper.GetQuery(strMemberQuery);
            return (DataSet)SqlDbHelper.RunQuery(cmd);       
        }
        #endregion
    }
}
